package indi.yangshenhui.tool.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import org.apache.log4j.Logger;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.fastjson.JSON;
import com.sun.rowset.CachedRowSetImpl;
import indi.yangshenhui.tool.PropertiesUtil;

public class DBHelper {
	private static Logger logger = Logger.getLogger(DBHelper.class);
	private static Properties properties = PropertiesUtil.getInstance().getProperties("jdbc.properties");
	private static DruidDataSource dataSource;

	static{
		if (dataSource == null) {
			dataSource = new DruidDataSource();
		}
		String driver = properties.getProperty("connection.driverclass");
		String url = properties.getProperty("connection.url");
		String user = properties.getProperty("connection.username");
		String password = properties.getProperty("connection.password");
		
		dataSource.setDriverClassName(driver);
		dataSource.setUsername(user);
		dataSource.setPassword(password);
		dataSource.setUrl(url);
	}
	
	public static Connection getConnection() throws SQLException {
		Connection connection = null;
		
		/*try {
			Class.forName(driver);
			connection = DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
			logger.error("获取数据库连接异常:" + e.getMessage(), e);
		}*/
		connection = dataSource.getConnection();
		
		return connection;
	}

	public static void main(String[] args) throws SQLException {
		getConnection();
	}

	/**
	 * 增删改
	 * 
	 * @param sql
	 * @return
	 */
	public static int executeNonQuery(String sql) {
		int result = 0;
		Connection connection = null;
		Statement statement = null;
		try {
			connection = getConnection();
			statement = connection.createStatement();
			result = statement.executeUpdate(sql);
			logger.info(sql);
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			free(null, statement, connection);
		}
		return result;
	}

	/**
	 * 增删改
	 * 
	 * @param sql
	 * @param obj
	 * @return
	 */
	public static int executeNonQuery(String sql, Object... obj) {
		int result = 0;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = getConnection();
			preparedStatement = connection.prepareStatement(sql);
			for (int i = 0; i < obj.length; i++) {
				preparedStatement.setObject(i + 1, obj[i]);
			}
			result = preparedStatement.executeUpdate();
			// logger.info(sql);
		} catch (SQLException e) {
			logger.error(JSON.toJSONString(obj));
			logger.error(e.getMessage(), e);
		} finally {
			free(null, preparedStatement, connection);
		}
		return result;
	}

	/**
	 * 查
	 * 
	 * @param sql
	 * @return
	 */
	public static CachedRowSetImpl executeQuery(String sql) {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		CachedRowSetImpl rowSet = null;
		try {
			connection = getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			rowSet = new CachedRowSetImpl();
			rowSet.populate(resultSet);
			logger.info(sql);
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			free(resultSet, statement, connection);
		}
		return rowSet;
	}

	/**
	 * 查
	 * 
	 * @param sql
	 * @param obj
	 * @return
	 */
	public static CachedRowSetImpl executeQuery(String sql, Object... obj) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		CachedRowSetImpl rowSet = null;
		try {
			connection = getConnection();
			preparedStatement = connection.prepareStatement(sql);
			for (int i = 0; i < obj.length; i++) {
				preparedStatement.setObject(i + 1, obj[i]);
			}
			resultSet = preparedStatement.executeQuery();
			rowSet = new CachedRowSetImpl();
			rowSet.populate(resultSet);
			logger.info(sql);
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			free(resultSet, preparedStatement, connection);
		}
		return rowSet;
	}

	/**
	 * 判断记录是否存在
	 * 
	 * @param sql
	 * @return
	 */

	public static Boolean isExist(String sql) {
		CachedRowSetImpl rowSet = null;
		try {
			rowSet = executeQuery(sql);
			rowSet.last();
			int count = rowSet.getRow();
			logger.info(sql);
			if (count > 0) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			logger.warn(e);
			free(rowSet);
			return false;
		} finally {
			free(rowSet);
		}
	}

	/**
	 * 判断记录是否存在
	 * 
	 * @param sql
	 * @param obj
	 * @return
	 */

	public static Boolean isExist(String sql, Object... obj) {
		CachedRowSetImpl rowSet = null;
		try {
			rowSet = executeQuery(sql, obj);
			rowSet.last();
			int count = rowSet.getRow();
			logger.info(sql);
			if (count > 0) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			free(rowSet);
			return false;
		} finally {
			free(rowSet);
		}
	}

	/**
	 * 获取查询记录的总行数
	 * 
	 * @param sql
	 * @return
	 */

	public static int getCount(String sql) {
		int result = 0;
		CachedRowSetImpl rowSet = null;
		try {
			rowSet = executeQuery(sql);
			rowSet.last();
			result = rowSet.getInt(1);
			logger.info(sql);
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			free(rowSet);
		}
		return result;
	}

	/**
	 * 获取查询记录的总行数
	 * 
	 * @param sql
	 * @param obj
	 * @return
	 */
	public static int getCount(String sql, Object... obj) {
		int result = 0;
		CachedRowSetImpl rowSet = null;
		try {
			rowSet = executeQuery(sql, obj);
			rowSet.last();
			result = rowSet.getInt(1);
			logger.info(sql);
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			free(rowSet);
		}
		return result;
	}

	public static void free(ResultSet resultSet) {
		try {
			if (resultSet != null) {
				resultSet.close();
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}
	}

	public static void free(Statement statement) {
		try {
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}
	}

	public static void free(Connection connection) {
		try {
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}
	}

	public static void free(ResultSet resultSet, Statement statement, Connection connection) {
		free(resultSet);
		free(statement);
		free(connection);
	}

}
